/**
 * File name:      DataBaseDaoImpl.java
 * Date:           2013-3-25
 * Description:    // 用于详细说明此程序文件完成的主要功
 *                 // 能与其他模块或函数的接口，输出值、
 *                 // 取值范围、含义及参数间的关系
 * Modify History:     Date             Programmer       Notes
 *                    ---------        ---------------  ---------
 *                    2013-3-25            伍冲                              Initial
 **********************************************************************/
package com.wy.framework.dao;

import java.beans.PropertyVetoException;
import java.io.File;
import java.io.IOException;
import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import net.sf.ehcache.Cache;
import net.sf.ehcache.CacheManager;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.Resource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.orm.hibernate4.HibernateTemplate;
import org.springframework.orm.hibernate4.support.HibernateDaoSupport;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.wy.framework.pojos.TSysDbRouter;
import com.wy.business.market.market.services.MarketAppsManageService;
import com.wy.component.file.FileUtil;

/**
 * Created on 2013-3-25
 * <p>
 * Title: [数据集成层数据库操纵对象实现]
 * </p>
 * <p>
 * Description: [数据库操纵对象实现]
 * </p>
 * 
 * @version 1.0
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
public class DataBaseDaoImpl implements IDataBaseDao {
	
	private static Logger logger = LoggerFactory.getLogger(DataBaseDaoImpl.class);


	@Autowired
	private HibernateDaoSupport hibernateDao;
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	private Resource cacheConfigFile;
	
	/**
	 * jdbcTemplateMap:（欧尚分布式DB数据源工厂）
	 * @since Ver 1.1
	 */
	private Map jdbcTemplateFactory = new HashMap();

	@Override
	public HibernateTemplate getHibernateTemplate() {
		return hibernateDao.getHibernateTemplate();
	}
	
	public JdbcTemplate getJdbcTemplateDistributeByIpSegment(String ips) {
		return (JdbcTemplate) jdbcTemplateFactory.get(ips);
	}
	
	/**
	 * Function Name               getJdbcTemplateDistribute                                   
	 * @param tSysDbRouter  数据库连接信息
	 * @return JdbcTemplate         		             
	 * @description (从工厂取得各门店数据源,以进行读写操作)
	 * Modify History:              Date             Programmer       Notes
	 *                            ---------        ---------------  ---------
	 *                      2016-2-3 下午9:01:53           张旭星                     Initial
	 **********************************************************************
	 */
	public JdbcTemplate getJdbcTemplateDistribute(TSysDbRouter tSysDbRouter) {
		if(tSysDbRouter==null){
			return null;
		}
		//从工厂取jt
		JdbcTemplate jdbcTemplate = (JdbcTemplate) jdbcTemplateFactory.get(tSysDbRouter.getIpSegment());
		if(jdbcTemplate != null){
			return jdbcTemplate;
		}
		// 创建ComboPooledDataSource对象 
		ComboPooledDataSource ds = new ComboPooledDataSource();
		try {
			ds.setDriverClass(tSysDbRouter.getMemdiandriver());
			ds.setJdbcUrl(tSysDbRouter.getMemdianurl());
			ds.setUser(tSysDbRouter.getMemdianuser());
			ds.setPassword(tSysDbRouter.getMemdianpwd());
			ds.setInitialPoolSize(5);
			ds.setMinPoolSize(5);
			ds.setMaxPoolSize(20);
			ds.setMaxIdleTime(10000);
			ds.setIdleConnectionTestPeriod(5);
			ds.setAcquireIncrement(1);
			//自动重连
			ds.setTestConnectionOnCheckin(true);
			ds.setIdleConnectionTestPeriod(60);
			JdbcTemplate jdbcTemplateObj = new JdbcTemplate(ds);
			//jt存工厂
			jdbcTemplateFactory.put(tSysDbRouter.getIpSegment(), jdbcTemplateObj);
			return jdbcTemplateObj;
		} catch (PropertyVetoException e1) {
			e1.printStackTrace();
		}
		return null; 
	}
	
	/**
	 * 刷新Hibernate缓存对象到数据库中
	 */
	@Override
	public void flush() throws ErrorSqlException {
		try {
			getHibernateTemplate().flush();
		} catch (DataAccessException e) {
			throw new ErrorSqlException("Hibernate 写入对象时发生错误", e);
		} catch (Exception e) {
			throw new ErrorSqlException("Hibernate 写入对象时发生错误", e);
		}
	}

	/**
	 * Load数据库数据到Hibernate缓存
	 */
	@Override
	public void refresh(Object transientObject) throws ErrorSqlException {
		try {
			getHibernateTemplate().refresh(transientObject);
		} catch (DataAccessException e) {
			throw new ErrorSqlException("Hibernate 刷新"
					+ transientObject.getClass().getName() + "对象时发生错误", e);
		} catch (Exception e) {
			throw new ErrorSqlException("Hibernate 刷新"
					+ transientObject.getClass().getName() + "对象时发生错误", e);
		}
	}

	@Override
	public void save(Object transientObject) throws ErrorSqlException {
		try {
			getHibernateTemplate().save(transientObject);
		} catch (DataAccessException e) {
			e.printStackTrace();
			throw new ErrorSqlException("Hibernate执行保存"
					+ transientObject.getClass().getName() + "对象时发生错误"
					+ e.getMessage(), e);
		} catch (Exception e) {
			e.printStackTrace();
			throw new ErrorSqlException("Hibernate执行保存"
					+ transientObject.getClass().getName() + "对象时发生错误"
					+ e.getMessage(), e);
		}

	}

	@Override
	public void update(Object transientObject) throws ErrorSqlException {
		try {
			getHibernateTemplate().update(transientObject);
		} catch (DataAccessException e) {
			e.printStackTrace();
			throw new ErrorSqlException("Hibernate执行更新"
					+ transientObject.getClass().getName() + "对象时发生错误"
					+ e.getMessage(), e);
		} catch (Exception e) {
			e.printStackTrace();
			throw new ErrorSqlException("Hibernate执行更新"
					+ transientObject.getClass().getName() + "对象时发生错误"
					+ e.getMessage(), e);
		}

	}

	@Override
	public void delete(Object transientObject) throws ErrorSqlException {
		try {
			getHibernateTemplate().delete(transientObject);
		} catch (DataAccessException e) {
			e.printStackTrace();
			throw new ErrorSqlException("Hibernate执行删除"
					+ transientObject.getClass().getName() + "对象时发生错误"
					+ e.getMessage(), e);
		} catch (Exception e) {
			e.printStackTrace();
			throw new ErrorSqlException("Hibernate执行删除"
					+ transientObject.getClass().getName() + "对象时发生错误"
					+ e.getMessage(), e);
		}
	}

	@Override
	public Object load(Class className, Serializable pk)
			throws ErrorSqlException {
		try {
			return getHibernateTemplate().get(className, pk);
		} catch (DataAccessException e) {
			throw new ErrorSqlException("Hibernate加载 " + className.getName()
					+ " 对象时发生错误" + e.getMessage(), e);
		} catch (Exception e) {
			throw new ErrorSqlException("Hibernate加载 " + className.getName()
					+ " 对象时发生错误" + e.getMessage(), e);
		}
	}

	@Override
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	@Override
	public void execute(String statement) throws ErrorSqlException {
		try {
			getJdbcTemplate().execute(statement);
		} catch (DataAccessException e) {
			throw new ErrorSqlException("JDBC 执行Sql发生错误,Sql" + statement, e);
		} catch (Exception e) {
			throw new ErrorSqlException("JDBC 执行Sql发生错误,Sql" + statement, e);
		}

	}

	@Override
	public int queryForInt(String sqlKey, List<Object> params)
			throws ErrorSqlException {
		try {
			if (params == null) {
				return getJdbcTemplate().queryForObject(getSql(sqlKey), Integer.class);
			} else {
				return getJdbcTemplate().queryForObject(getSql(sqlKey), params.toArray(), Integer.class);
			}
		} catch (DataAccessException e) {
			if (params == null) {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：null" + e.getMessage(), e);
			} else {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：" + params.toString() + e.getMessage(), e);
			}
		} catch (Exception e) {
			if (params == null) {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：null" + e.getMessage(), e);
			} else {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：" + params.toString() + e.getMessage(), e);
			}
		}
	}

	@Override
	public List<Map> query(String sqlKey, List<Object> params, RowMapper rm)
			throws ErrorSqlException {
		try {
			if (params == null) {
				return getJdbcTemplate().query(getSql(sqlKey), new Object[] {},
						rm);
			} else {
				return getJdbcTemplate().query(getSql(sqlKey),
						params.toArray(), rm);
			}
		} catch (DataAccessException e) {
			if (params == null) {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：null" + e.getMessage(), e);
			} else {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：" + params.toString() + e.getMessage(), e);
			}
		} catch (Exception e) {
			if (params == null) {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：null" + e.getMessage(), e);
			} else {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：" + params.toString() + e.getMessage(), e);
			}
		}
	}

	@Override
	public List queryForList(String sqlKey, List<Object> params)
			throws ErrorSqlException {
		try {
			if (params == null) {
				return getJdbcTemplate().queryForList(getSql(sqlKey));
			} else {
				return getJdbcTemplate().queryForList(getSql(sqlKey), params.toArray());
			}
		} catch (DataAccessException e) {
			if (params == null) {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：null" + e.getMessage(), e);
			} else {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：" + params.toString() + e.getMessage(), e);
			}
		} catch (Exception e) {
			if (params == null) {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：null" + e.getMessage(), e);
			} else {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：" + params.toString() + e.getMessage(), e);
			}
		}

	}
	
	@Override
	public List queryForListAuchan(String sqlKey, List<Object> params, JdbcTemplate jdbcTemplate)
			throws ErrorSqlException {
//		try {
			if (params == null) {
				return jdbcTemplate.queryForList(getSql(sqlKey));
			} else {
				return jdbcTemplate.queryForList(getSql(sqlKey), params.toArray());
			}
//		} catch (DataAccessException e) {
//			if (params == null) {
//				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
//						+ " 传入参数：null" + e.getMessage(), e);
//			} else {
//				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
//						+ " 传入参数：" + params.toString() + e.getMessage(), e);
//			}
//		} catch (Exception e) {
//			if (params == null) {
//				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
//						+ " 传入参数：null" + e.getMessage(), e);
//			} else {
//				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
//						+ " 传入参数：" + params.toString() + e.getMessage(), e);
//			}
//		}
	}
	
	@Override
	public List queryForListAuchan(String sqlKey, List<Object> params, JdbcTemplate jdbcTemplate,String appendSql)
			throws ErrorSqlException {
		if (params == null) {
			return jdbcTemplate.queryForList(getSql(sqlKey) + appendSql);
		} else {
			return jdbcTemplate.queryForList(getSql(sqlKey) + appendSql, params.toArray());
		}
	}
	
	@Override
	public List queryForListOrder(String sqlKey, List<Object> params, String orderSql)
			throws ErrorSqlException {
		try {
			if (params == null) {
				return getJdbcTemplate().queryForList(getSql(sqlKey));
			} else {
				String a = getSql(sqlKey);
				a = a.replace("ORDER BY ?", "ORDER BY "+orderSql);
				return getJdbcTemplate().queryForList(a,
						params.toArray());
			}
		} catch (DataAccessException e) {
			if (params == null) {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：null" + e.getMessage(), e);
			} else {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：" + params.toString() + e.getMessage(), e);
			}
		} catch (Exception e) {
			if (params == null) {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：null" + e.getMessage(), e);
			} else {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：" + params.toString() + e.getMessage(), e);
			}
		}

	}
	
	@Override
	public List queryForListIn(String sqlKey,List<Object> param,String inStr) throws ErrorSqlException{
		try {
			if (param == null) {
				String a = getSql(sqlKey);
				a=a.replace("(?)", "("+inStr+")");
				return getJdbcTemplate().queryForList(a);
			} else {
				String a = getSql(sqlKey);
				a=a.replace("(?)", "("+inStr+")");
				return getJdbcTemplate().queryForList(a,param.toArray());
			}
		} catch (DataAccessException e) {
			if (param == null) {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：null" + e.getMessage(), e);
			} else {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：" + param.toString() + e.getMessage(), e);
			}
		} catch (Exception e) {
			if (param == null) {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：null" + e.getMessage(), e);
			} else {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：" + param.toString() + e.getMessage(), e);
			}
		}
	}

	@Override
	public List queryNoKeyForList(String sqlstr, List<Object> params)
			throws ErrorSqlException {
		try {
			if (params == null) {
				return getJdbcTemplate().queryForList(sqlstr);
			} else {
				return getJdbcTemplate().queryForList(sqlstr, params.toArray());
			}
		} catch (DataAccessException e) {
			if (params == null) {
				throw new ErrorSqlException("JDBC 执行查询发生错误,sqlstr=" + sqlstr
						+ " 传入参数：null" + e.getMessage(), e);
			} else {
				throw new ErrorSqlException("JDBC 执行查询发生错误,sqlstr=" + sqlstr
						+ " 传入参数：" + params.toString() + e.getMessage(), e);
			}
		} catch (Exception e) {
			if (params == null) {
				throw new ErrorSqlException("JDBC 执行查询发生错误,sqlstr=" + sqlstr
						+ " 传入参数：null" + e.getMessage(), e);
			} else {
				throw new ErrorSqlException("JDBC 执行查询发生错误,sqlstr=" + sqlstr
						+ " 传入参数：" + params.toString() + e.getMessage(), e);
			}
		}

	}
	

	@Override
	public List queryForList(String sqlKey, List<Object> params,
			String sqlstr) throws ErrorSqlException {
		try {
			if (params == null) {
				return getJdbcTemplate().queryForList(getSql(sqlKey) + sqlstr);
			} else {
				return getJdbcTemplate().queryForList(getSql(sqlKey) + sqlstr,
						params.toArray());
			}
		} catch (DataAccessException e) {
			if (params == null) {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：null" + e.getMessage(), e);
			} else {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：" + params.toString() + e.getMessage(), e);
			}
		} catch (Exception e) {
			if (params == null) {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：null" + e.getMessage(), e);
			} else {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：" + params.toString() + e.getMessage(), e);
			}
		}

	}
	
	@Override
	public List queryForListReplaceStr(String sqlKey, List<Object> params,
			Map replaceStrMap) throws ErrorSqlException {
		try {
			String tempSql = getSql(sqlKey);
			Set<Map.Entry> set = replaceStrMap.entrySet();
			for (Iterator<Map.Entry> it = set.iterator(); it.hasNext();) {
				Map.Entry entry = (Map.Entry) it.next();
				tempSql = tempSql.replaceAll(entry.getKey().toString(), entry.getValue().toString());
			}

			if (params == null) {
				return getJdbcTemplate().queryForList(tempSql);
			} else {
				return getJdbcTemplate().queryForList(tempSql, params.toArray());
			}
		} catch (DataAccessException e) {
			if (params == null) {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：null" + e.getMessage(), e);
			} else {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：" + params.toString() + e.getMessage(), e);
			}
		} catch (Exception e) {
			if (params == null) {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：null" + e.getMessage(), e);
			} else {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：" + params.toString() + e.getMessage(), e);
			}
		}
		
	}

	@Override
	public List<Map> queryForPage(String sqlKey, List<Object> params,
			int pageNum, int rowNumPerPage) throws ErrorSqlException {
		return null;
	}
	
	public String getDatabaseTypeName() {
		DatabaseMetaData md;
		try {
			md = this.jdbcTemplate.getDataSource().getConnection().getMetaData();
			return md.getDatabaseProductName();  
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return "";
	}

	public void init() {
		Document Config = null;
		SAXReader saxReader = new SAXReader();
		try {
			//主干sqlconfig*.xml
			String sqlConfigFilePath = this.getClass().getClassLoader().getResource("/").getPath() + "/resources/component/";
			sqlConfigFilePath += "sqlconfig-"+getDatabaseTypeName()+".xml";
			Config = saxReader.read(sqlConfigFilePath);
			Element Sqls = Config.getRootElement();
			List<?> SqlList = new ArrayList();
			SqlList.addAll(Sqls.elements());
			//分支sqlconfig*.xml
			String branchPath = this.getClass().getClassLoader().getResource("/").getPath() + "/com";
			List exts = new ArrayList();
			exts.add("xml");
			List<File> filterList = FileUtil.getAllFiles(new ArrayList(), exts, "sqlconfig", branchPath);
			if(filterList != null && filterList.size()>0){
				for(File file : filterList){
					Config = saxReader.read(file.getPath());
					Sqls = Config.getRootElement();
					SqlList.addAll(Sqls.elements());
				}
			}
			System.out.println("==================================================");
			System.out.println("                开始加载 查询语句缓存配置 [SqlConfig.xml]     ");
			System.out.println("==================================================");
			CacheManager cacheManager = CacheManager.create(cacheConfigFile.getURL());
			Cache cacheSql = cacheManager.getCache("CacheSql");
			synchronized (cacheSql) {
				for (int i = 0; i < SqlList.size(); i++) {
					String key = ((Element) SqlList.get(i)).attributeValue(
							"sqlkey").trim();
					String describ = ((Element) SqlList.get(i)).attributeValue(
							"describ").trim();
					String sqlStr = ((Element) SqlList.get(i)).getTextTrim()
							.trim();
					net.sf.ehcache.Element element = new net.sf.ehcache.Element(
							key, sqlStr);
					cacheSql.put(element);
					System.out.println("                加载 " + describ);
					System.out
							.println("--------------------------------------------------");
				}
			}
			System.out.println();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (DocumentException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * Method Description:根据key值获取sql语句
	 * 
	 * @param:String key
	 * @return:String sql
	 */
	public String getSql(String key) {
		CacheManager cacheManager = CacheManager.getInstance();
		Cache cacheSql = cacheManager.getCache("CacheSql");
		synchronized (cacheSql) {
			return (String) cacheSql.get(key).getValue();
		}
	}

	/**
	 * @return the hibernateDao
	 */
	public HibernateDaoSupport getHibernateDao() {
		return hibernateDao;
	}

	/**
	 * @return the jdbcDao
	 */
//	public JdbcDaoSupport getJdbcDao() {
//		return jdbcDao;
//	}

	/**
	 * @param hibernateDao
	 *            the hibernateDao to set
	 */
	public void setHibernateDao(HibernateDaoSupport hibernateDao) {
		this.hibernateDao = hibernateDao;
	}

	/**
	 * @param jdbcDao
	 *            the jdbcDao to set
	 */
//	public void setJdbcDao(JdbcDaoSupport jdbcDao) {
//		this.jdbcDao = jdbcDao;
//	}

//	public Resource getSqlConfigFile() {
//		return sqlConfigFile;
//	}

//	public void setSqlConfigFile(Resource sqlConfigFile) {
//		this.sqlConfigFile = sqlConfigFile;
//	}

	public Resource getCacheConfigFile() {
		return cacheConfigFile;
	}

	public void setCacheConfigFile(Resource cacheConfigFile) {
		this.cacheConfigFile = cacheConfigFile;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.cfets.project.dao.IDataBaseDao#update(java.lang.String,
	 * java.util.List)
	 */
	@Override
	public void update(String sqlKey, List<Object> params)
			throws ErrorSqlException {
		getJdbcTemplate().update(getSql(sqlKey), params.toArray());

	}

	@Override
	public void saveOrUpdate(Object transientObject) throws ErrorSqlException {
		try {
			getHibernateTemplate().saveOrUpdate(transientObject);
		} catch (DataAccessException e) {
			e.printStackTrace();
			throw new ErrorSqlException("Hibernate执行更新或保存"
					+ transientObject.getClass().getName() + "对象时发生错误"
					+ e.getMessage(), e);
		} catch (Exception e) {
			e.printStackTrace();
			throw new ErrorSqlException("Hibernate执行更新或保存"
					+ transientObject.getClass().getName() + "对象时发生错误"
					+ e.getMessage(), e);
		}
	}

	/* (non-Javadoc)
	 * @see com.cfets.project.dao.IDataBaseDao#queryForList(java.lang.String, java.lang.String)
	 */
	@Override
	public List queryForList(String sqlKey, String param)
			throws ErrorSqlException {
		return getJdbcTemplate().queryForList(getSql(sqlKey), new Object[]{param});
	}

	/* (non-Javadoc)
	 * @see com.cfets.project.dao.IDataBaseDao#queryForList(java.lang.String)
	 */
	@Override
	public List queryForList(String sqlKey) throws ErrorSqlException {
		return getJdbcTemplate().queryForList(getSql(sqlKey));
	}

	/* (non-Javadoc)
	 * @see com.cfets.project.dao.IDataBaseDao#delete(java.lang.Class, java.io.Serializable)
	 */
	@Override
	public void delete(Class className, Serializable pk)
			throws ErrorSqlException {
		Object obj = load(className, pk);
		if(obj == null){
			System.out.println("不存在" + className + ":" + pk);
		}
		else{
			delete(obj);
		}
	}

	/* (non-Javadoc)
	 * @see com.cfets.project.dao.IDataBaseDao#update(java.lang.String)
	 */
	@Override
	public void update(String sqlKey) throws ErrorSqlException {
		getJdbcTemplate().update(getSql(sqlKey));
	}

	/* (non-Javadoc)
	 * @see com.cfets.project.dao.IDataBaseDao#update(java.lang.String, java.lang.String)
	 */
	@Override
	public void update(String sqlKey, String key) throws ErrorSqlException {
		getJdbcTemplate().update(getSql(sqlKey), new Object[]{key});
	}
	
	@Override
	public List queryByJdbcByJdbcTemplate(String sql, Object[] params, final Class[] clz) throws DataAccessException, ErrorSqlException{
		List list =  getJdbcTemplate().query(sql, params, new RowMapper(){

			@Override
			public Object mapRow(ResultSet rs, int arg1) {
				Object[] obj = new Object[clz.length];
				try{
					for(int i=0; i<clz.length; i++){
						obj[i] = getRsValue(rs, clz[i], i+1);
					}
				}catch(Exception e ){
					throw new IllegalStateException(e);
				}
				return obj;
			}
			
		});
		
		return list;
	}
	
	protected static Object getRsValue(ResultSet rs, Class cls, int index) throws SQLException, InstantiationException, IllegalAccessException {
		Object retVal = null;
		if (String.class == cls)
			retVal = rs.getString(index);
		else if (Integer.class== cls || Integer.TYPE == cls)
			retVal = new Integer(rs.getInt(index));
		else if (Long.class== cls || Long.TYPE== cls)
			retVal = new Long(rs.getLong(index));
		else if (Short.class== cls || Short.TYPE == cls) 
			retVal = new Short(rs.getShort(index));
//		else if (Date.class== cls || java.sql.Date.class== cls)
//			retVal = rs.getTimestamp(index);
		else if (Date.class== cls || java.sql.Date.class== cls){
			
		}
		else if (BigDecimal.class== cls)
			retVal = rs.getBigDecimal(index);
		else if (Boolean.class== cls)
			retVal = new Boolean(rs.getBoolean(index));
		else if (Character.class== cls)
			retVal = rs.getString(index);
		else if(Double.class== cls)
			retVal = new Double(rs.getDouble(index));
		else if(Float.class== cls)
			retVal = new Float(rs.getFloat(index));	
		else if(Time.class == cls)
			retVal = rs.getTime(index);
		else
			throw new IllegalArgumentException("未识别类型" + cls);
		if(rs.wasNull()) 
			return null;
		if(Date.class == cls){
			retVal = new Date(((Timestamp)retVal).getTime());
		}else if(java.sql.Date.class == cls){
			retVal = new java.sql.Date(((Timestamp)retVal).getTime());
		}
		
		return retVal;
	}
	
	
	/**
	 * 
	 * Function Name               queryForPageList                                   
	 * @param sqlKey
	 * @param pageSize
	 * @param pageNum
	 * @param params
	 * @return
	 * @throws Exception          		             
	 * @description ORACLE分页
	 * Modify History:              Date             Programmer       Notes
	 *                            ---------        ---------------  ---------
	 *                      2016-4-19 上午10:34:00           黄忠                     Initial
	 **********************************************************************
	 */
	@SuppressWarnings({ "unchecked", "unused" })
	public List<Map> queryForPageList(String sqlKey, Integer pageSize, Integer pageNum, List params) throws Exception	{
		List list = new ArrayList();
		Map map = new HashMap();
		if(params == null) {
			params = new ArrayList();
		}
		
		String sql= getSql(sqlKey);
		
		try {
			String sqlForCount = "select count(*) from ( " + sql + " ) ";
			Integer totalNum = getJdbcTemplate().queryForObject(sqlForCount,params.toArray(), Integer.class);
			map.put("total", totalNum);
			
			int maxMath=pageNum*pageSize;
			int minMath=pageNum*pageSize-pageSize+1;
			params.add(maxMath);
			params.add(minMath);
			
			String dataSql = " select * from (select a.*, rownum rn from ( " + sql + " ) a where rownum <= ? ) where rn >=? "; 
			map.put("rows",  getJdbcTemplate().queryForList(dataSql,params.toArray()));
			list.add(map);
		} catch (DataAccessException e) {
			if (params == null) {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：null" + e.getMessage(), e);
			} else {
				throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
						+ " 传入参数：" + params.toString() + e.getMessage(), e);
			}
		}
		return list;
		
	} 
	
	/**
	 * 
	 * Function Name               querySQLByPage                                   
	 * @param sqlKey 
	 * @param pageSize
	 * @param pageNum
	 * @param conditionSql 条件sql
	 * @param params 条件参数
	 * @return
	 * @throws Exception          		             
	 * @description 多条件分页查询方法
	 * Modify History:              Date             Programmer       Notes
	 *                            ---------        ---------------  ---------
	 *                      2016-5-16 上午9:36:21           黄忠                     Initial
	 **********************************************************************
	 */
	public List<Map> querySQLByPage(String sqlKey, Integer pageSize, Integer pageNum, String conditionSql, List params) throws Exception {
		
		List list = new ArrayList();
		Map map = new HashMap();
		
		if(params == null) {
			params = new ArrayList();
		}
		
		String sql= getSql(sqlKey);
		try {
			String sqlForCount = "select count(*) from ( " + sql + conditionSql + " ) ";
			
			Integer totalNum = getJdbcTemplate().queryForObject(sqlForCount,params.toArray(), Integer.class);
			
			logger.info("执行查询总行数 totalNum:   " + totalNum);
			map.put("total", totalNum);
			
			int maxMath=pageNum*pageSize;
			int minMath=pageNum*pageSize-pageSize+1;
			params.add(maxMath);
			params.add(minMath);
			
			String dataSql = " select * from (select a.*, rownum rn from ( " + sql + conditionSql + " ) a where rownum <= ? ) where rn >=? "; 
			map.put("rows",  getJdbcTemplate().queryForList(dataSql,params.toArray()));
			
			logger.info("执行分页查询sql："+dataSql);
			list.add(map);
		} catch (DataAccessException e) {
			throw new ErrorSqlException("JDBC 执行查询发生错误,Sqlkey=" + sqlKey
					+ " 传入参数：" + params.toString() + e.getMessage(), e);
		}
		
		return list;
	}
	
}
